package com.tuanzi.loan.business.repository.miniApps.recommend.impl;

import static com.tuanzi.loan.generated.jooq.loan.tables.LoanMiniAppsRelation.LOAN_MINI_APPS_RELATION;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import org.apache.commons.lang3.StringUtils;
import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.impl.TableImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import com.google.common.collect.Lists;
import com.tuanzi.loan.business.entity.miniApps.articleLibrary.MiniAppsRelation;
import com.tuanzi.loan.business.repository.miniApps.articleLibrary.HealthHeadlineRepository;
import com.tuanzi.loan.business.repository.miniApps.recommend.HealthHeadlineRecommendRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.miniApps.recommend.HealthHeadlineRecommendRequest;
import com.tuanzi.loan.vo.response.miniApps.recommend.HealthHeadlineRecommendResponse;
import com.tuanzi.loan.vo.view.miniApps.recommend.HealthHeadlineRecommendInfoView;
import com.tuanzi.server.loan_base.util.DateUtils;

@Repository
public class HealthHeadlineRecommendRepositoryImpl extends JOOQBaseRepositoryPlus implements HealthHeadlineRecommendRepository{
	
	@Autowired
	private HealthHeadlineRepository headlineRepository;
	
	@Override
	protected TableImpl getTable() {
		return LOAN_MINI_APPS_RELATION;
	}
	
	public void cleanCache() {
		headlineRepository.cleanCache();
	}
	
	public List<MiniAppsRelation> getMiniAppsList() {
		return headlineRepository.getMiniAppsList();
	}
	
	public List<MiniAppsRelation> getAdvertiseList() {
		return headlineRepository.getAdvertiseList();
	}
	
	
	public HealthHeadlineRecommendInfoView getAdvertiseArticleView(Long articleRelationId) {
		List<Object> paramList = Lists.newArrayList();
		StringBuffer sbf = new StringBuffer();
		sbf.append(" SELECT advertise.ID AS article_relation_id ,advertise.RELATION_ID as relation_id ,advertise.WEIGHT as weight ,article.ID as article_id , ");
		sbf.append(" advertise.ONLINE_TIME as online_date, advertise.UPDATOR as operator ,advertise.status as advertise_status ");
		sbf.append(" FROM loan.loan_mini_apps_article_relations advertise ,loan.loan_mini_apps_article article ");
		sbf.append(" WHERE advertise.ID = ? AND advertise.ARTICLE_ID = article.ID ");
		
		paramList.add(articleRelationId);
		
		List<MiniAppsRelation> list = dslContext.fetch(sbf.toString(), paramList.toArray()).map(new RecordMapper<Record, MiniAppsRelation>() {
			@Override
			public MiniAppsRelation map(Record record) {
				MiniAppsRelation relation = new MiniAppsRelation();
				relation.setId(record.get("id", Long.class));
				relation.setRelationCode(record.get("relation_code", String.class));
				relation.setRelationName(record.get("relation_name", String.class));
				relation.setType(record.get("type", String.class));
				return relation;
			}
		});
		
		return null;
	}
	
	public Page<HealthHeadlineRecommendResponse> getListPage(HealthHeadlineRecommendRequest searchRequest, Pageable pageable) {
		List<Object> paramList = Lists.newArrayList();
		StringBuilder  sql = new StringBuilder(" SELECT advertise.article_relation_id as article_relation_id,advertise.RELATION_ID as relation_id ,advertise.relation_code as relation_code, ");
		sql.append(" advertise.RELATION_NAME as relation_name , article.ID as article_id ,advertise.WEIGHT as weight ,article.TITLE as title , ");
		sql.append(" advertise.ONLINE_TIME AS online_time ,advertise.advertise_status as advertise_status ,advertise.UPDATOR as operator ");
		sql.append(" FROM (SELECT ar.ID AS article_relation_id,rr.ID AS relation_id ,rr.RELATION_CODE AS relation_code ,rr.RELATION_NAME , ar.ARTICLE_ID , ");
		sql.append(" ar.WEIGHT ,ar.ONLINE_TIME ,ar.OFFLINE_TIME ,ar.STATUS AS advertise_status ,ar.UPDATOR ");  
		sql.append(" FROM loan.loan_mini_apps_article_relations ar ,loan.loan_mini_apps_relation rr ");  
		sql.append(" WHERE ar.RELATION_ID = rr.ID AND rr.TYPE = 'ADVERTISE') advertise , loan.loan_mini_apps_article article ");  
		sql.append(" WHERE advertise.ARTICLE_ID = article.ID ");  
		sql.append(" AND EXISTS (SELECT 1 FROM loan.loan_mini_apps_article_relations ar ,loan.loan_mini_apps_relation rr ");   
		sql.append(" WHERE ar.RELATION_ID = rr.ID AND rr.TYPE = 'SORT' AND ar.STATUS = 1 AND article.ID = ar.ARTICLE_ID )   ");
		
		if(Objects.nonNull(searchRequest.getStartRecommendDate())) { // 推荐上线时间
			sql.append(" AND advertise.ONLINE_TIME >= ? ");
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest.getStartRecommendDate()));
		}
		if(Objects.nonNull(searchRequest.getEndtRecommendDate())) {  // 推荐下线时间
			sql.append(" AND advertise.ONLINE_TIME <= ? ");
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest.getEndtRecommendDate()));
		}
		if (StringUtils.isNotEmpty(searchRequest.getTitle())) { // 文章标题
			sql.append(" AND article.TITLE LIKE ? ");
			paramList.add("%"+searchRequest.getTitle()+"%");
		}
		if (!(Objects.isNull(searchRequest.getMiniAppsCodeList()) || searchRequest.getMiniAppsCodeList().isEmpty())) { // 小程序
			sql.append(" AND EXISTS (SELECT 1 FROM loan.loan_mini_apps_article_relations ar ,loan.loan_mini_apps_relation rr ");
			sql.append(" WHERE ar.RELATION_ID = rr.ID AND rr.TYPE = 'MINI_APPS' AND rr.RELATION_CODE in ("+listToSqlPart(searchRequest.getMiniAppsCodeList())+") AND article.ID = ar.ARTICLE_ID ) ");
		}
		if (!(Objects.isNull(searchRequest.getAdvertiseList()) || searchRequest.getAdvertiseList().isEmpty())) { // 广告位
			sql.append(" AND advertise.relation_code in ("+listToSqlPart(searchRequest.getAdvertiseList())+") ");
		}
		if (StringUtils.isNotEmpty(searchRequest.getRecommendStatus())) { // 广告位状态
			sql.append(" AND advertise.advertise_status = ? ");
			paramList.add(searchRequest.getRecommendStatus());
		}
		
		sql.append(" ORDER BY advertise.ONLINE_TIME DESC ");
		
		Long count = dslContext.fetchOne("SELECT COUNT(*) FROM (" + sql + ") AS i", paramList.toArray()).into(Long.class);
		
		paramList.add(pageable.getOffset());
		paramList.add(pageable.getPageSize());
		
		return new PageImpl<HealthHeadlineRecommendResponse>(dslContext.fetch(sql + " LIMIT ?,? ", paramList.toArray()).map(new RecordMapper<Record, HealthHeadlineRecommendResponse>() {
			@Override
			public HealthHeadlineRecommendResponse map(Record record) {
				HealthHeadlineRecommendResponse response = new HealthHeadlineRecommendResponse();
				Long articleRelationId = record.get("article_relation_id", Long.class);
				Long relationId = record.get("relation_id", Long.class);
				Long articleId = record.get("article_id", Long.class);
				
				response.setArticleRelationId(articleRelationId);
				response.setRelationId(relationId);
				response.setArticleId(articleId);
				response.setAdvertiseCode(record.get("relation_code", String.class));
				response.setAdvertiseName(record.get("relation_name", String.class));
				response.setWeight(record.get("weight", Integer.class));
				response.setArticleTitle(record.get("title",String.class));
				response.setAdvertiseOnlineDate(record.get("online_time" ,Date.class));
				response.setAdvertiseStatus(record.get("advertise_status" ,String.class));
				response.setUpdator(record.get("operator" ,String.class));
				
				// 多个小程序需要优化，现在只针对一个小程序来做
				response.setMiniAppsListText(listToString(headlineRepository.getMiniAppsListOfArticle(articleId)));
				return response;
			}
		}), pageable, count);
	}
	
	/**
	 * list列表转换为文本字符串
	 * @param list
	 * @return
	 */
	private String listToString(List<MiniAppsRelation> list) {
		if(list == null || list.size() == 0) {
			return null;
		}
		StringBuffer sbf = new StringBuffer();
		for(MiniAppsRelation relation : list) {
			if(sbf.length() == 0) {
				sbf.append(relation.getRelationName());
			}
			sbf.append(","+relation.getRelationName());
		}
		return sbf.toString();
	}
	
	private String listToSqlPart(List<String> list) {
		if(list == null || list.size() == 0) {
			return "";
		}
		StringBuffer sbf = new StringBuffer();
		for(String s : list) {
			if(sbf.length() == 0) {
				sbf.append("'"+s+"'");
			} else {
				sbf.append(",'"+s+"'");
			}
		}
		return sbf.toString();
	}

}
























